﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListStaffCategoryDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class ListStaffCategoryBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListStaffCategory; } }

        private UserInformation userOrganization;

        private ListStaffCategoryMaster listStaffCategoryMaster;

        private BindingList<ListStaffCategoryDetail> listStaffCategoryDetailList;



        public ListStaffCategoryBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();

                listStaffCategoryMaster = new ListStaffCategoryMaster();

                this.listStaffCategoryDetailList = new BindingList<ListStaffCategoryDetail>();

                GlobalDefaultValue.Apply(this);


                this.ListStaffCategoryMaster.PropertyChanged += new PropertyChangedEventHandler(ListStaffCategoryMaster_PropertyChanged);

                this.ListStaffCategoryDetailList.ListChanged += new ListChangedEventHandler(ListStaffCategoryDetailList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListStaffCategoryMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListStaffCategoryDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private ListStaffCategoryListingTableAdapter listingTableAdapter;
        protected ListStaffCategoryListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new ListStaffCategoryListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private ListStaffCategoryTableAdapter masterTableAdapter;
        protected ListStaffCategoryTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListStaffCategoryTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListStaffCategoryDetailTableAdapter detailTableAdapter;
        protected ListStaffCategoryDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListStaffCategoryDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public ListStaffCategoryDTS.ListStaffCategoryListingDataTable ListStaffCategoryListing()
        {
            return this.ListingTableAdapter.GetData();
        }

        public ListStaffCategoryMaster ListStaffCategoryMaster
        {
            get { return this.listStaffCategoryMaster; }
        }

        public BindingList<ListStaffCategoryDetail> ListStaffCategoryDetailList
        {
            get { return this.listStaffCategoryDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int StaffCategoryID   //Primary Key
        {
            get { return this.ListStaffCategoryMaster.StaffCategoryID; }
            private set
            {
                if (this.ListStaffCategoryMaster.StaffCategoryID != value)
                {
                    this.StopTracking();

                    this.ListStaffCategoryMaster.StaffCategoryID = value;

                    this.ListStaffCategoryGetMaster();
                    this.ListStaffCategoryGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListStaffCategoryGetMaster()
        {
            if (this.StaffCategoryID > 0)
            {
                ListStaffCategoryDTS.ListStaffCategoryDataTable masterDataTable = this.MasterTableAdapter.GetData(this.StaffCategoryID);

                if (masterDataTable.Count > 0)
                {
                    this.ListStaffCategoryMaster.StopTracking();

                    this.ListStaffCategoryMaster.EntryDate = masterDataTable[0].EntryDate;

                    this.ListStaffCategoryMaster.AncestorID = masterDataTable[0].AncestorID;
                    this.ListStaffCategoryMaster.SalesmenID = masterDataTable[0].SalesmenID;

                    this.ListStaffCategoryMaster.Description = masterDataTable[0].Description;
                    this.ListStaffCategoryMaster.Other = masterDataTable[0].Other;

                    this.ListStaffCategoryMaster.InActive = masterDataTable[0].InActive;

                    this.ListStaffCategoryMaster.StartTracking();

                    this.ListStaffCategoryMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.ListStaffCategoryMaster);
                this.ListStaffCategoryMaster.EntryDate = DateTime.Today;
                this.ListStaffCategoryMaster.Reset();
            }
        }


        private void ListStaffCategoryGetDetail()
        {
            this.listStaffCategoryDetailList.RaiseListChangedEvents = false;
            this.listStaffCategoryDetailList.Clear();
            if (this.StaffCategoryID >= 0)
            {

                ListStaffCategoryDTS.ListStaffCategoryDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.StaffCategoryID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListStaffCategoryDTS.ListStaffCategoryDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listStaffCategoryDetailList.Add(new ListStaffCategoryDetail(detailRow.CommonID, detailRow.CommonValue, detailRow.Remarks));
                    }
                }
            }
            this.listStaffCategoryDetailList.RaiseListChangedEvents = true;
            this.listStaffCategoryDetailList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListStaffCategoryDetail> inValidListStaffCategoryDetail = this.ListStaffCategoryDetailList.Where(listStaffCategoryDetail => !listStaffCategoryDetail.IsValid).ToList();
                return this.ListStaffCategoryMaster.IsValid && inValidListStaffCategoryDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListStaffCategoryEditable", this.StaffCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListStaffCategoryVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListStaffCategoryEditable", this.StaffCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListStaffCategoryUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListStaffCategoryEditable", this.StaffCategoryID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int salesmenCategoryID)
        {
            if (this.StaffCategoryID == salesmenCategoryID) this.StaffCategoryID = -1; //Enforce to reload
            this.StaffCategoryID = salesmenCategoryID;
        }

        public void New()
        {
            if (this.StaffCategoryID == 0) this.StaffCategoryID = -1;
            this.StaffCategoryID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int salesmenCategoryID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref salesmenCategoryID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(salesmenCategoryID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    transactionScope.Complete();
                }

                SQLDatabase.ExecuteNonQuery("ListStaffCategoryUpdateHierarchyLevel", CommandType.StoredProcedure, "AncestorID", this.listStaffCategoryMaster.AncestorID);

                this.Fill(salesmenCategoryID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listStaffCategoryMaster.StaffCategoryID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listStaffCategoryMaster.StaffCategoryID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listStaffCategoryMaster.StaffCategoryID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");

                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListStaffCategoryMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int salesmenCategoryID)
        {
            ListStaffCategoryDTS.ListStaffCategoryDataTable masterDataTable;
            ListStaffCategoryDTS.ListStaffCategoryRow masterRow;

            if (this.listStaffCategoryMaster.StaffCategoryID <= 0) //Add
            {
                masterDataTable = new ListStaffCategoryDTS.ListStaffCategoryDataTable();
                masterRow = masterDataTable.NewListStaffCategoryRow();
            }
            else //Edit
            {
                if (!this.SaveUndo(listStaffCategoryMaster.StaffCategoryID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listStaffCategoryMaster.StaffCategoryID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.EntryDate = this.listStaffCategoryMaster.EntryDate;

            masterRow.AncestorID = this.listStaffCategoryMaster.AncestorID;
            masterRow.HierarchyLevel = 0;
            masterRow.HierarchyOrder = "";

            masterRow.SalesmenID = this.listStaffCategoryMaster.SalesmenID;

            masterRow.Description = this.listStaffCategoryMaster.Description;
            masterRow.Other = this.listStaffCategoryMaster.Other;

            masterRow.InActive = false;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listStaffCategoryMaster.StaffCategoryID <= 0) masterDataTable.AddListStaffCategoryRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            salesmenCategoryID = masterRow.StaffCategoryID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int salesmenCategoryID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            ListStaffCategoryDTS.ListStaffCategoryDetailDataTable detailDataTable = new ListStaffCategoryDTS.ListStaffCategoryDetailDataTable();

            foreach (ListStaffCategoryDetail listStaffCategoryDetail in this.ListStaffCategoryDetailList)
            {
                ListStaffCategoryDTS.ListStaffCategoryDetailRow detailRow = detailDataTable.NewListStaffCategoryDetailRow();

                detailRow.StaffCategoryID = salesmenCategoryID;
                detailRow.SerialID = ++serialID;

                detailRow.CommonID = listStaffCategoryDetail.CommonID;
                detailRow.CommonValue = listStaffCategoryDetail.CommonValue;

                detailRow.Remarks = listStaffCategoryDetail.Remarks;

                detailDataTable.AddListStaffCategoryDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.listStaffCategoryDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int salesmenCategoryID)
        {
            this.DetailTableAdapter.Delete(salesmenCategoryID);

            return true;
        }



        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;



            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, Description, AncestorID, Other, EntryDate " + "\r\n";
            queryString = queryString + "       FROM        ListStaffCategory " + "\r\n";
            queryString = queryString + "       ORDER BY    Description " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryListing", queryString);



            queryString = "     @StaffCategoryID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListStaffCategory " + "\r\n";
            queryString = queryString + "       WHERE       StaffCategoryID = @StaffCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategorySelect", queryString);


            queryString = "     @Description nvarchar(100),	@AncestorID int, @HierarchyLevel int, @HierarchyOrder nvarchar(50), @SalesmenID int, @Other nvarchar(100), @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListStaffCategory (Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID) VALUES (@Description, @AncestorID, @HierarchyLevel, @HierarchyOrder, @SalesmenID, @Other, @InActive, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID FROM ListStaffCategory WHERE StaffCategoryID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryInsert", queryString);


            queryString = "     @StaffCategoryID int, @Description nvarchar(100), @AncestorID int, @HierarchyLevel int, @HierarchyOrder nvarchar(50), @SalesmenID int, @Other nvarchar(100), @InActive bit, @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListStaffCategory SET Description = @Description, AncestorID = @AncestorID, HierarchyLevel = @HierarchyLevel, HierarchyOrder = @HierarchyOrder, SalesmenID = @SalesmenID, Other = @Other, InActive = @InActive, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE StaffCategoryID = @StaffCategoryID " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, Description, AncestorID, HierarchyLevel, HierarchyOrder, SalesmenID, Other, InActive, EntryDate, UserID, UserOrganizationID FROM ListStaffCategory WHERE StaffCategoryID = @StaffCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryUpdate", queryString);


            queryString = " @StaffCategoryID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListStaffCategory WHERE StaffCategoryID = @StaffCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListStaffCategoryDelete", queryString);






            queryString = " @StaffCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, SerialID, CommonID, CommonValue, Remarks " + "\r\n";
            queryString = queryString + "       FROM        ListStaffCategoryDetail " + "\r\n";
            queryString = queryString + "       WHERE       StaffCategoryID = @StaffCategoryID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryDetailSelect", queryString);


            queryString = " @StaffCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListStaffCategoryDetail (StaffCategoryID, SerialID, CommonID, CommonValue, Remarks) VALUES (@StaffCategoryID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListStaffCategoryDetail WHERE (StaffCategoryID = @StaffCategoryID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryDetailInsert", queryString);



            queryString = " @StaffCategoryID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListStaffCategoryDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE StaffCategoryID = @StaffCategoryID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      StaffCategoryID, SerialID, CommonID, CommonValue, Remarks FROM ListStaffCategoryDetail WHERE StaffCategoryID = @StaffCategoryID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryDetailUpdate", queryString);



            queryString = " @StaffCategoryID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListStaffCategoryDetail WHERE StaffCategoryID = @StaffCategoryID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListStaffCategoryDetailDelete", queryString);



            queryString = " @AncestorID Int ";
            queryString = queryString + " WITH ENCRYPTION ";
            queryString = queryString + " AS ";

            queryString = queryString + "   DECLARE @AncestorHierarchyOrder NVarchar(30), @AncestorHierarchyLevel Int  ";
            queryString = queryString + "   DECLARE @Cursor_Rows Int, @SerialNo Int ";

            queryString = queryString + "   SET @AncestorHierarchyOrder = N'' ";
            queryString = queryString + "   SET @AncestorHierarchyLevel = 0 ";
            queryString = queryString + "   SET @SerialNo = 0 ";
            queryString = queryString + "   SET @Cursor_Rows = 0 ";

            queryString = queryString + "   IF @AncestorID <> 0 ";
            queryString = queryString + "   BEGIN ";

            queryString = queryString + "       DECLARE Ancestor_Cursor CURSOR LOCAL FOR ";
            queryString = queryString + "       SELECT TOP 1 HierarchyOrder, HierarchyLevel FROM ListStaffCategory ";
            queryString = queryString + "       WHERE StaffCategoryID = @AncestorID ";

            queryString = queryString + "       OPEN Ancestor_Cursor ";

            queryString = queryString + "       FETCH NEXT FROM Ancestor_Cursor INTO @AncestorHierarchyOrder, @AncestorHierarchyLevel ";

            queryString = queryString + "       CLOSE Ancestor_Cursor ";
            queryString = queryString + "       DEALLOCATE Ancestor_Cursor ";


            queryString = queryString + "   END ";

            queryString = queryString + "   DECLARE @StaffCategoryID Int ";

            queryString = queryString + "   DECLARE StaffCategory_Cursor CURSOR LOCAL SCROLL FOR ";
            queryString = queryString + "   SELECT StaffCategoryID FROM ListStaffCategory ";
            queryString = queryString + "   WHERE AncestorID = @AncestorID ORDER BY Description ";
            queryString = queryString + "   FOR UPDATE ";

            queryString = queryString + "   OPEN StaffCategory_Cursor ";

            queryString = queryString + "   FETCH LAST FROM StaffCategory_Cursor INTO @StaffCategoryID ";
            queryString = queryString + "   IF @@FETCH_STATUS = 0 ";
            queryString = queryString + "   BEGIN ";
            queryString = queryString + "       SET @Cursor_Rows = @@CURSOR_ROWS ";
            queryString = queryString + "       FETCH FIRST FROM StaffCategory_Cursor INTO @StaffCategoryID ";
            queryString = queryString + "   END ";

            queryString = queryString + "   WHILE @@FETCH_STATUS = 0";
            queryString = queryString + "   BEGIN";

            queryString = queryString + "       SET @SerialNo = @SerialNo + 1 ";

            queryString = queryString + "       UPDATE ListStaffCategory SET HierarchyOrder = @AncestorHierarchyOrder +  REPLICATE('0', DATALENGTH(CONVERT (NVarchar(30), @Cursor_Rows)) - DATALENGTH(CONVERT (NVarchar(30), @SerialNo))) + CONVERT (NVarchar(30), @SerialNo), HierarchyLevel = @AncestorHierarchyLevel + 1 ";
            queryString = queryString + "       WHERE CURRENT OF StaffCategory_Cursor ";

            queryString = queryString + "       EXECUTE SPStaffCategorySaveDetail @AncestorID = @StaffCategoryID ";

            queryString = queryString + "       FETCH NEXT FROM StaffCategory_Cursor INTO @StaffCategoryID ";

            queryString = queryString + "   END ";

            queryString = queryString + "   CLOSE StaffCategory_Cursor ";
            queryString = queryString + "   DEALLOCATE StaffCategory_Cursor ";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryUpdateHierarchyLevel", queryString);




            queryString = " @AncestorID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            queryString = queryString + "   DECLARE @AncestorHierarchyLevel Int  " + "\r\n";

            queryString = queryString + "   SET @AncestorHierarchyLevel = 0 " + "\r\n";

            queryString = queryString + "   IF @AncestorID <> 0 " + "\r\n";
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE Ancestor_Cursor CURSOR LOCAL FOR " + "\r\n";
            queryString = queryString + "       SELECT TOP 1 HierarchyLevel FROM ListStaffCategory " + "\r\n";
            queryString = queryString + "       WHERE StaffCategoryID = @AncestorID " + "\r\n";

            queryString = queryString + "       OPEN Ancestor_Cursor " + "\r\n";

            queryString = queryString + "       FETCH NEXT FROM Ancestor_Cursor INTO @AncestorHierarchyLevel " + "\r\n";

            queryString = queryString + "       CLOSE Ancestor_Cursor " + "\r\n";
            queryString = queryString + "       DEALLOCATE Ancestor_Cursor " + "\r\n";

            queryString = queryString + "   END " + "\r\n";

            queryString = queryString + "   DECLARE @StaffCategoryID Int " + "\r\n";

            queryString = queryString + "   DECLARE StaffCategory_Cursor CURSOR LOCAL SCROLL FOR " + "\r\n";
            queryString = queryString + "   SELECT StaffCategoryID FROM ListStaffCategory " + "\r\n";
            queryString = queryString + "   WHERE AncestorID = @AncestorID ORDER BY Description " + "\r\n";
            queryString = queryString + "   FOR UPDATE " + "\r\n";

            queryString = queryString + "   OPEN StaffCategory_Cursor " + "\r\n";

            queryString = queryString + "   FETCH LAST FROM StaffCategory_Cursor INTO @StaffCategoryID " + "\r\n";
            queryString = queryString + "   IF @@FETCH_STATUS = 0 " + "\r\n";
            queryString = queryString + "   BEGIN " + "\r\n";
            queryString = queryString + "       FETCH FIRST FROM StaffCategory_Cursor INTO @StaffCategoryID " + "\r\n";
            queryString = queryString + "   END " + "\r\n";

            queryString = queryString + "   WHILE @@FETCH_STATUS = 0 " + "\r\n";
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       UPDATE ListStaffCategory SET HierarchyLevel = @AncestorHierarchyLevel + 1 " + "\r\n";
            queryString = queryString + "       WHERE CURRENT OF StaffCategory_Cursor " + "\r\n";

            queryString = queryString + "       EXECUTE ListStaffCategoryUpdateHierarchyLevel @AncestorID = @StaffCategoryID " + "\r\n";

            queryString = queryString + "       FETCH NEXT FROM StaffCategory_Cursor INTO @StaffCategoryID " + "\r\n";

            queryString = queryString + "   END " + "\r\n";

            queryString = queryString + "   CLOSE StaffCategory_Cursor " + "\r\n";
            queryString = queryString + "   DEALLOCATE StaffCategory_Cursor " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListStaffCategoryUpdateHierarchyLevel", queryString);




            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[2];            
            queryArray[0] = "   SELECT      TOP 1 StaffCategoryID FROM ListStaffCategory WHERE AncestorID = @FindIdentityID " ;
            queryArray[1] = "   SELECT      TOP 1 StaffCategoryID FROM ListStaffName WHERE StaffCategoryID = @FindIdentityID " ;

            SQLDatabase.CreateProcedureToCheckExisting("ListStaffCategoryEditable", queryArray);

        }
    }
}
